package fr.aracore.domotique.zapi.db;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.axis.DateAxis;
import org.jfree.chart.axis.DateTickUnit;
import org.jfree.chart.axis.DateTickUnitType;
import org.jfree.chart.axis.NumberAxis;
import org.jfree.chart.axis.ValueAxis;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.plot.XYPlot;
import org.jfree.chart.renderer.xy.StandardXYItemRenderer;
import org.jfree.data.category.CategoryDataset;
import org.jfree.data.jdbc.JDBCCategoryDataset;
import org.jfree.data.xy.AbstractXYDataset;
import org.jfree.data.xy.XYDataset;

import fr.aracore.domotique.zapi.model.Sensor;



public class DBManager {
	private static DBManager dbManager;
	
	private Connection connection;
	
	public static DBManager dbManager(){
		if (dbManager==null)
			dbManager=new DBManager();
		return dbManager;
	}
	private DBManager(){
	}
	
	public Connection connection(){
		if (connection!=null) return connection;
		
		try {
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection("jdbc:sqlite:test.db");
			connection.setAutoCommit(false);
		} catch ( Exception e ) {
			System.err.println( e.getClass().getName() + ": " + e.getMessage() );
			return null;
		}
		System.out.println("Opened database successfully");
		return connection;
	}
	
	public void createStructure() throws SQLException{
		Statement stmt = connection().createStatement();
		String sql = "CREATE TABLE DEVICE " +
				"(ID CHAR(25) PRIMARY KEY     NOT NULL," +
				" TYPE           CHAR(5)    NOT NULL, " + 
				" NUMBER         CHAR(20)    NOT NULL, " + 
				" LASTMESSAGE    TEXT    NOT NULL)";
		stmt.executeUpdate(sql);
		sql = "CREATE TABLE SENSOR " +
				"(TAG           CHAR(8)    NOT NULL, " + 
				" VALUE         CHAR(20)    NOT NULL, " + 
				" ID_DEVICE		CHAR(25) NOT NULL," +
				" TIMESTAMP		INTEGER NOT NULL)";
		stmt.executeUpdate(sql);
		connection().commit();
		stmt.close();
	}
	
	public void updateSensor(Sensor sensor) throws SQLException{
		Statement stmt = connection().createStatement();
		//looking for device
		String sql = "SELECT * FROM DEVICE WHERE ID='"+sensor.device().idDevice()+"'";
		ResultSet rs = stmt.executeQuery(sql);
		if (!rs.next()){
			//No device found
			sql = "INSERT INTO DEVICE (ID,TYPE,NUMBER,LASTMESSAGE) " +
		          "VALUES ('"+sensor.device().idDevice()+"', '"+sensor.device().type+"', '"+sensor.device().number
		          +"', '"+sensor.device().lastMessage+"' );"; 
			stmt.executeUpdate(sql);
			connection().commit();
		}
		sql = "INSERT INTO SENSOR (TAG,VALUE,ID_DEVICE,TIMESTAMP) " +
		          "VALUES ('"+sensor.tag()+"', '"+sensor.value()+"', '"+sensor.device().idDevice()+"',"+System.currentTimeMillis()+" );"; 
		stmt.executeUpdate(sql);
		connection().commit();
		stmt.close();
	}
	
	public void testDrawGraph(File outputFile,Sensor sensor) throws SQLException, IOException{
		String sql="select timestamp,value from sensor where tag='"+sensor.tag()+"' and id_device='"+sensor.device().idDevice()+"' order by timestamp";
		CategoryDataset lcDataset=new JDBCCategoryDataset(connection(), sql);
		JFreeChart lcChart=ChartFactory.createLineChart(Sensor.descriptionForTag(sensor.tag()), "date", Sensor.descriptionForTag(sensor.tag()),lcDataset , PlotOrientation.VERTICAL, false, false, false);
		ChartUtilities.saveChartAsPNG(outputFile, lcChart, 1300, 1300);
	}
	
	public void testDrawGraph(File outputFile,String tag,String device_id) throws SQLException, IOException{
		Statement stmt = connection().createStatement();
		String sql="select timestamp,value from sensor where tag='"+tag+"' and id_device='"+device_id+"' order by timestamp";
		ResultSet rs = stmt.executeQuery(sql);
		ArrayList<ArrayList<Number[]>> lcData=new ArrayList<ArrayList<Number[]>>();

		ArrayList<Number[]> lcSerie=new ArrayList<Number[]>();
		while (rs.next()){
			Number timestamp=rs.getLong("timestamp");
			String value=rs.getString("value");
			try{
			Number lcValue=Float.parseFloat(value);
			lcSerie.add(new Number[]{timestamp,lcValue});
			}
			catch(NumberFormatException e){
				
			}
		}
		if (!lcSerie.isEmpty())
			lcData.add(lcSerie);
		if (!lcData.isEmpty()){
			XYDataset lcDataset=new Dataset(lcData);
			DateAxis xAxis = new DateAxis("Date");
			xAxis.setDateFormatOverride(new SimpleDateFormat("HH:mm dd-MM"));
			xAxis.setTickUnit(new DateTickUnit(DateTickUnitType.MINUTE, 15));
			xAxis.setVerticalTickLabels(true);
			
			ValueAxis valueAxis = new NumberAxis(Sensor.descriptionForTag(tag));

			XYPlot plot = new XYPlot(lcDataset, xAxis, valueAxis,new StandardXYItemRenderer());
			plot.setDomainCrosshairVisible(true);
			plot.setRangeCrosshairVisible(true);
			plot.setOrientation(PlotOrientation.VERTICAL);
			JFreeChart lcChart = new JFreeChart(Sensor.descriptionForTag(tag), JFreeChart.DEFAULT_TITLE_FONT,
					plot, false);

			ChartFactory.getChartTheme().apply(lcChart);
			ChartUtilities.saveChartAsPNG(outputFile, lcChart, 800, 800);
		}
	}


	
	public void testDrawGraph(File outputFile,String device_id) throws SQLException, IOException{
		Statement stmt = connection().createStatement();
		String sql="select tag,timestamp,value from sensor where id_device='"+device_id+"' order by 1,2";
		ResultSet rs = stmt.executeQuery(sql);
		ArrayList<ArrayList<Number[]>> lcData=new ArrayList<ArrayList<Number[]>>();

		String currentTag="";
		ArrayList<Number[]> lcSerie=new ArrayList<Number[]>();
		while (rs.next()){
			String tag=rs.getString("tag");
			if (!currentTag.equals(tag)){
				if (!lcSerie.isEmpty())
					lcData.add(lcSerie);
				lcSerie=new ArrayList<Number[]>();
				currentTag=tag;
			}
			Number timestamp=rs.getLong("timestamp");
			String value=rs.getString("value");
			try{
			Number lcValue=Float.parseFloat(value);
			lcSerie.add(new Number[]{timestamp,lcValue});
			}
			catch(NumberFormatException e){
				
			}

		}
		if (!lcData.isEmpty()){
			XYDataset lcDataset=new Dataset(lcData);
			
			DateAxis xAxis = new DateAxis("Date");
			xAxis.setDateFormatOverride(new SimpleDateFormat("HH:mm dd-MM"));
			xAxis.setTickUnit(new DateTickUnit(DateTickUnitType.MINUTE, 15));
			xAxis.setVerticalTickLabels(true);
			
			ValueAxis valueAxis = new NumberAxis();
//			ValueAxis axis2 = new NumberAxis("Secondary");

			XYPlot plot = new XYPlot(lcDataset, xAxis, valueAxis,new StandardXYItemRenderer());
//			plot.setRangeAxis(1, axis2);
//	        plot.mapDatasetToRangeAxis(1, 1);
	        
			plot.setDomainCrosshairVisible(true);
			plot.setRangeCrosshairVisible(true);
			plot.setOrientation(PlotOrientation.VERTICAL);
			JFreeChart lcChart = new JFreeChart(device_id, JFreeChart.DEFAULT_TITLE_FONT,
					plot, false);

			ChartFactory.getChartTheme().apply(lcChart);
			ChartUtilities.saveChartAsPNG(outputFile, lcChart, 800, 800);
		}
	}
	
	
	
//	public void testDrawGraph() throws SQLException, IOException{
//		String sql="select distinct tag,id_device from sensor";
//		Statement stmt = connection().createStatement();
//		ResultSet rs = stmt.executeQuery(sql);
//		while (rs.next()){
//			String tag=rs.getString("tag");
//			String device_id=rs.getString("id_device");
//			testDrawGraph(new File(device_id+"-"+tag+".png"), tag, device_id);
//		}
//	}
	public void testDrawGraph() throws SQLException, IOException{
		String sql="select distinct id_device from sensor";
		Statement stmt = connection().createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()){
			String device_id=rs.getString("id_device");
			testDrawGraph(new File(device_id+".png"), device_id);
		}
	}
	
	
	
	public boolean dbStorage() {
		return true;
	}
	
	
	class Dataset extends AbstractXYDataset{

		List<List<Number[]>> data;
		
		public Dataset(List data){
			this.data=data;
		}
		
		@Override
		public int getItemCount(int serie) {
			return data.get(serie).size();
		}

		@Override
		public Number getX(int serie, int item) {
			return (Number) data.get(serie).get(item)[0];
		}

		@Override
		public Number getY(int serie, int item) {
			return (Number) data.get(serie).get(item)[1];
		}

		@Override
		public int getSeriesCount() {
			return data.size();
		}

		@Override
		public Comparable getSeriesKey(int paramInt) {
			return paramInt+"";
		}
		
	}
}
